﻿using ExcelLibrary.SpreadSheet;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace SnmpForm.DbWrapper
{
    class ExcellibraryHelper
    {
        //使用单件模式
        static ExcellibraryHelper _instance = null;
        public static ExcellibraryHelper Instance
        {
            get
            {
                if (_instance == null)
                {
                    _instance = new ExcellibraryHelper();
                }
                return _instance;
            }
        }
        public int TemplateExport()
        {
            return List2ExcelSaved(null, DbDataTypes.ExcleTemplate.DBTableColunm, DbDataTypes.ExcleTemplate.ExcleTableColunm);
        }
        public bool APListImport(ref int nSuccess,ref int nFailure)
        {//excel进程关闭有问题
            bool bRet = false;
            List<object> list = null;

            if (ReadExcle2List(ref list, DbDataTypes.ExcleTemplate.DBTableColunm) == true)
            {
                if (list != null)
                {
                    nSuccess = DbWrapper.SQLDataOperate.Instance.SaveAPInfoList(list);
                    nFailure = list.Count-nSuccess;
                }
                else
                {
                    nSuccess = 0;
                    nFailure = 0;
                }
                bRet = true;
            }
            return bRet;
        }

        public int APListExport(int _uid, string _floorid)
        {
            List<object> list = null;
            int ret = -1;
            list = DbWrapper.SQLDataOperate.Instance.GetApInfoList(_uid, _floorid);
            if (list == null)
                return -1;
            ret = List2ExcelSaved(list, DbDataTypes.ExcleTemplate.DBTableColunm, DbDataTypes.ExcleTemplate.ExcleTableColunm);
            return ret;
        }
        public int APListExport(int _uid, List<int> listAP)
        {
            List<object> list = null;
            int ret = -1;
            list = DbWrapper.SQLDataOperate.Instance.GetApInfoList(_uid, listAP);
            if (list == null)
                return ret;
            ret = List2ExcelSaved(list, DbDataTypes.ExcleTemplate.DBTableColunm, DbDataTypes.ExcleTemplate.ExcleTableColunm);
            return ret;
        }

        private string m_filename;
        private Workbook m_workbook;
        private Worksheet m_worksheet;
        
        public ExcellibraryHelper(string filename = "")
        {
            m_filename = filename;
        }

        private void InitWrok()
        {

            m_workbook = new Workbook();
            m_worksheet = new Worksheet("FirstSheet");
            for (int i = 0; i < 30; i++)
            {
                for (int j = 0; j < 9; j++)
                {
                    m_worksheet.Cells[i, j] = new Cell("");
                }
            }
        }

        private void OpenWork()
        {
            m_workbook = Workbook.Load(m_filename);
            m_worksheet = m_workbook.Worksheets[0];
        }

        public int List2Excel(List<object> lsDetail, string[] columnName, string[] columnDiscription)
        {
            if (columnName.Length == 0 || columnName.Length != columnDiscription.Length)
            {
                return -1;
            }
            int nColLen = columnName.Length;
            InitWrok();

            //设置标题
            for (int nCol = 0; nCol < nColLen; nCol++)
            {
                m_worksheet.Cells[0, nCol] = new Cell(columnDiscription[nCol]);
            }

            //填充数据
            int nRow = 1;
            if (lsDetail == null) //导出模板
            {
                m_workbook.Worksheets.Add(m_worksheet);
                m_workbook.Save(m_filename);
                return 1;
            }
            foreach (object o in lsDetail)
            {
                Dictionary<string, object> entity = (Dictionary<string, object>)o;    
                for (int nCol = 0; nCol < nColLen; nCol++)
                {
                    if (nCol == DbDataTypes.ExcleTemplate.SNMP_PWD_INFEX)
                    {//snmp密码加密存储
                        try
                        {
                            m_worksheet.Cells[nRow, nCol] = new Cell(BusinessWrapper.AES.AESDecrypt(entity[columnName[nCol]].ToString()));
                        }
                        catch
                        {
                            m_worksheet.Cells[nRow, nCol] = new Cell("********");
                        }
                    }
                    else if (nCol == DbDataTypes.ExcleTemplate.FLOORD_INFEX)
                    {
                        if(entity[columnName[nCol]].ToString() == "unknown")
                            m_worksheet.Cells[nRow, nCol] = new Cell("");
                        else
                            m_worksheet.Cells[nRow, nCol] = new Cell(entity[columnName[nCol]]);
                    }
                    else
                    {
                        m_worksheet.Cells[nRow, nCol] = new Cell(entity[columnName[nCol]]);
                    }
                }
                nRow++;
            }
            m_workbook.Worksheets.Add(m_worksheet);
            m_workbook.Save(m_filename);
            return nRow -1;
        }

        public List<object> Excel2List(string[] columnDict)
        {
            OpenWork();

            /*      int rowIndex = m_worksheet.Cells.FirstRowIndex;
                 Row row = m_worksheet.Cells.GetRow(rowIndex);
                int nLastColIndex = row.LastColIndex;
                 while (nLastColIndex >= 0 && row.GetCell(nLastColIndex).ToString() == "")
                 {
                     nLastColIndex--;
                 }
                 if(nLastColIndex < 0 || nLastColIndex +1 !=  columnDict.Length)
                 {
                 }
                  * */
            int nColumnNum=0, nCol=0, nRow=0;
            while (true)
            {
                if (m_worksheet.Cells[nRow, nCol].ToString() == "")
                {
                    break;
                }
                nCol++;
            }
            nColumnNum = nCol;
            if (nColumnNum != columnDict.Length)
            {
                SQLDataOperate.Instance.WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                        DbDataTypes.LogInfo.TYPE.SYSTEM,
                        "导入模板错误");
                return null;
            }

            List<object> list = new List<object>();
            nRow = 1;
            nCol = 0;
            while (true)
            {
                if ((m_worksheet.Cells[nRow, 0]).ToString() == "")
                {
                    break;
                }
                Dictionary<string, object> entity = new Dictionary<string, object>();
                for (nCol = 0; nCol < nColumnNum; nCol++)
                {
                    if (nCol == DbDataTypes.ExcleTemplate.SNMP_PWD_INFEX)
                    {
                        try
                        {
                            string cipher = BusinessWrapper.AES.AESEncrypt((m_worksheet.Cells[nRow, nCol]).ToString());
                            entity.Add(columnDict[nCol], cipher);
                        }
                        catch
                        {
                            break;
                        }
                    }
                    else
                    {
                        entity.Add(columnDict[nCol], (m_worksheet.Cells[nRow, nCol]).ToString());
                    }
                }
                list.Add(entity);
                nRow++;
            }
            return list;
        }


        public int List2ExcelSaved(List<object> lsDetail, string[] columnName, string[] columnDiscription)
        {

            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.InitialDirectory = Application.ExecutablePath.ToString();
            saveDialog.FileName = "APManager.xls";
            saveDialog.Filter = "Excel文件(*.xls)|*.xls";
            saveDialog.FilterIndex = 1;
            saveDialog.RestoreDirectory = true;
            int ret = -1;
            try
            {
                if (saveDialog.ShowDialog() == DialogResult.OK)
                {
                    m_filename = saveDialog.FileName;
                    ret = List2Excel(lsDetail, columnName, columnDiscription); 
                }
                else
                {
                    ret = 0;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
                return -1;
            }
            return ret;
        }

        public bool ReadExcle2List(ref List<object> list,string[] column)
        {
            OpenFileDialog openDialog = new OpenFileDialog();
            openDialog.InitialDirectory = Application.ExecutablePath.ToString();
            openDialog.FileName = "APManager.xls";
            openDialog.Filter = "Excel文件(*.xls)|*.xls|所有文件（*.*)|*.*";
            openDialog.FilterIndex = 1;
            openDialog.RestoreDirectory = true;
            try
            {
                if (openDialog.ShowDialog() == DialogResult.OK)
                {
                    m_filename = openDialog.FileName;
                    list = Excel2List(column);
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
            return true ;
        }
    }
}
